'use strict';

const Service = require('egg').Service;
const moment = require('moment');

class ScoreService extends Service {
    //创建
    async add(username,skill,theory,num) {
        let time = new Date();
        await this.app.mysql.query('insert into scorelist (username,skill,theory,num,create_time) values (?,?,?,?,?)',[username,skill,theory,num,time]);
    }
    //修改
    async test(username,skill,theory,id){
        console.log(username,skill,theory,id)
        await this.app.mysql.query('update scorelist set username=?,skill=?,theory=? where id=?',[username,skill,theory,id])
    }
    //删除
    async del(id){
        await this.app.mysql.query('delete from scorelist where id=?',[id])
    }
    //查询
    async index(pagenum,limit,key){
        // let startIndex = (pagenum-1)*limit;

        //有条件 key  //没有条件  where

        //模糊搜索加分页  先要进行模糊搜索再进行分页

        let time = moment().format('YYYY-MM-DD');

        let sql = `select * from scorelist where`;

        if(key){
            sql += ` username like '%${key}%' and`;
        }

        sql += ` date_format(create_time,'%Y-%m-%d')=?`
        // sql += ` limit ${startIndex},${limit}`;

        return await this.app.mysql.query(sql,[time])
    }
    //查总数4
    async total(key){
        let time = moment().format('YYYY-MM-DD');
        let sql = 'select count(*) from scorelist where';
        if(key){
            sql += ` username like '%${key}%' and`
        }
        sql += ` date_format(create_time,'%Y-%m-%d')=?`
        return await this.app.mysql.query(sql,[time]);
    }
    async line(num){
        //指定人员  查询当月的
        let curTime = moment().format('YYYY-MM');
        return await this.app.mysql.query('select * from scorelist where num=? and date_format(create_time,"%Y-%m")=?',[num,curTime])
    }
    async bar(sNum,bNum,type){
        let time = moment().format('YYYY-MM-DD');
        //查询的是数量 今日技能每个阶段的人数

        let sql = 'select count(*) from scorelist where date_format(create_time,"%Y-%m-%d")=?';
        
        sql += ` and (${type}>=? and ${type}<=?)`
        
        return await this.app.mysql.query(sql,[time,sNum,bNum])
    }
}

module.exports = ScoreService;
